﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace QuanLyTienDien
{
    public partial class KhachHang : Form
    {
        private SqlConnection con;
        private DataTable dt = new DataTable("KhachHang");
        private SqlDataAdapter da = new SqlDataAdapter();
        
        //ham ket noi
        private bool IsConnect()
        {
            bool isOpend = false;
            string cn = "Data Source=THANHHA;Initial Catalog=QUANLYTIENDIEN;Integrated Security=True";
            try
            {
                //MessageBox.Show("ket noi thanh cong");
                con = new SqlConnection(cn);
                con.Open();//mo ket noi
                if (con.State == ConnectionState.Open)
                {
                    isOpend= true;
                }
                else
                isOpend = false;
            }
            catch (Exception )
            {
                MessageBox.Show("Không thể kết nối", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            return isOpend;
        }
        //ham dong ket noi
        private void disconnect()
        {
            con.Close();//dong ket noi
            con.Dispose();//giai phong tai nguyen
            con = null;//huy doi tuong

        }
        public KhachHang()
        {
            InitializeComponent();
        }

        private void HienThi()
        {
            SqlCommand com = new SqlCommand();//khai bao 1 command
            com.Connection = con;//ket noi
            com.CommandType = CommandType.Text;//khai bao kieu command
            com.CommandText = "SELECT * FROM KhachHang order by(MACONGTO) asc";
            da.SelectCommand = com;//gan command cho data
            da.Fill(dt);//nap dl len table
            dtgridkhachhang.DataSource = dt;//do dl len datagridview
        }
        //ham load DL
        private void LoadDaTa()
        {
            
            //viet theo kieu Bindings
            txtmact.DataBindings.Clear();//xoa DL co san trong textbox
            txtmact.DataBindings.Add("Text", dtgridkhachhang.DataSource, "MACONGTO");
            //Text:mac dinh,MACONGTO:ten cot trong datagridview
            txthoten.DataBindings.Clear();
            txthoten.DataBindings.Add("Text", dtgridkhachhang.DataSource, "HOTEN");
            txtsonha.DataBindings.Clear();
            txtsonha.DataBindings.Add("Text", dtgridkhachhang.DataSource, "SONHA");
           

        }
        //Bắt sự kiện chọn cả dòng
        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                dtgridkhachhang.CurrentRow.Selected = true;//DL đc chon ca dong
            }
            catch { }
        }
   
        private void Form1_Load(object sender, EventArgs e)
        {
            IsConnect();
            HienThi();
            LoadDaTa();
            disconnect();
            
        }

       //kich vao xuat bien lai ra form bien lai
        private void btnXuatBL_Click(object sender, EventArgs e)
        {
            BienLai bienlai = new BienLai();
            bienlai.Show();
        }
        

        private void btnThem_Click(object sender, EventArgs e)
        {
            ThemKH them = new ThemKH();
            them.Show();
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            int isInserted;
            if (IsConnect())
            {
                string ma = txtmact.Text;
                string hoten = txthoten.Text;
                string sonha = txtsonha.Text;
                SqlCommand comUpdate = new SqlCommand();
                comUpdate.CommandText = @"UPDATE KhachHang SET 
                MACONGTO=" +ma+",HOTEN=N'"+hoten+"',SONHA="+sonha+" WHERE MACONGTO="+ma+"";
                comUpdate.Connection = con;
                comUpdate.CommandType = CommandType.Text;
                isInserted = comUpdate.ExecuteNonQuery();//thuc thi truy van va khong can du lieu tra ve ap dung cho cau lenh insert, delete, update
                if (isInserted > 0)
                {
                    
                    MessageBox.Show("Sửa thành công ");
                    
                }
                else
                {
                    MessageBox.Show("Chưa sửa được");
                }
            }
            else
            {
                MessageBox.Show("Không thể kết nối ");
            }
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            int isInserted;
            if (IsConnect())
            {
                string ma = txtmact.Text;
                string hoten = txthoten.Text;
                string sonha = txtsonha.Text;
                SqlCommand comDelete = new SqlCommand();
                comDelete.CommandText = @"DELETE FROM KhachHang WHERE MACONGTO=" + ma + "";
                comDelete.Connection = con;
                comDelete.CommandType = CommandType.Text;
                isInserted = comDelete.ExecuteNonQuery();
                if (isInserted > 0)
                {
                    MessageBox.Show("Xoá thành công ");
                }
                else
                {
                    MessageBox.Show("Chưa xoá được");
                }
            }
            else
            {
                MessageBox.Show("Không thể kết nối");
            }
        }
        
       
        //Refresh
        private void button2_Click(object sender, EventArgs e)
        {
            if (IsConnect())
            {
                SqlCommand comInsert = new SqlCommand();
                comInsert.CommandText = @"SELECT * FROM KhachHang order by(MACONGTO) asc";

                comInsert.Connection = con;
                comInsert.CommandType = CommandType.Text;
                SqlDataAdapter adap = new SqlDataAdapter(comInsert);
                DataTable tab = new DataTable();
                adap.Fill(tab);
                dtgridkhachhang.DataSource = tab;
                dtgridkhachhang.Refresh();
                LoadDaTa();
            }
           
           
        }
        //THOÁT TOÀN CHƯƠNG TRÌNH
        private void button1_Click(object sender, EventArgs e)
        {
            Close();//thoát
            Dispose();//giải phóng bộ nhớ
            MessageBox.Show("Bạn có chắc chắn thoát không?", "Thoát");
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
             int isInserted;
                if (IsConnect())
                {

                   
                    string sonha = textBox1.Text;
                    SqlCommand comInsert = new SqlCommand();
                    comInsert.CommandText = @"SELECT * FROM KhachHang WHERE SONHA=" +sonha+ "";
                    comInsert.Connection = con;
                    comInsert.CommandType = CommandType.Text;
                    isInserted = comInsert.ExecuteNonQuery();//thuc thi truy van va khong can du lieu tra ve ap dung cho cau lenh insert, delete, update
                    
                }
                else
                {
                    MessageBox.Show("Không thể kết nối");
                }

                Close();
                disconnect();
                Dispose();
                
            
        }
       
    }
}
